using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using GoogleEarthClasses.Global;
namespace GoogleEarthClasses.Data
{
    public class DPoint
    {
        public static DataTable SelectAll(DateTime dStart, DateTime dEnd)
        {
            SqlDataAdapter _DataAdapter = null;
            try
            {
                _DataAdapter = new SqlDataAdapter("sp_Positions_SelectAll", DBHelper.GetConnection());
                _DataAdapter.SelectCommand.Parameters.Add(DBHelper.CreateDateTimeParameter("@dStart", ParameterDirection.Input, true, dStart));
                _DataAdapter.SelectCommand.Parameters.Add(DBHelper.CreateDateTimeParameter("@dEnd", ParameterDirection.Input, true, dEnd));
                _DataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable _Dt = new DataTable();
                _DataAdapter.Fill(_Dt);
                return _Dt;
            }
            catch (SqlException sex)
            {
                throw sex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_DataAdapter != null) _DataAdapter.SelectCommand.Connection.Close();
            }
        }
        public static DataTable SelectAllPoints()
        {
            SqlDataAdapter _DataAdapter = null;
            try
            {
                _DataAdapter = new SqlDataAdapter("sp_Positions_SelectAll", DBHelper.GetConnection());
                _DataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable _Dt = new DataTable();
                _DataAdapter.Fill(_Dt);
                return _Dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_DataAdapter != null) _DataAdapter.SelectCommand.Connection.Close();
            }
        }
        public static Int32 Insert(double longitude, double latitude, double altitude, string description, DateTime dTime, int CountSatellites, double angle, string color)
        {
            SqlCommand _Command = null;
            SqlTransaction _Transaction = null;
            try
            {
                _Command = new SqlCommand("sp_Positions_Insert", DBHelper.GetConnection());
                _Transaction = _Command.Connection.BeginTransaction();
                _Command.Transaction = _Transaction;
                _Command.CommandType = CommandType.StoredProcedure;
                #region description
                if (string.IsNullOrEmpty(description))
                    _Command.Parameters.Add(DBHelper.CreateTextParameter("@tDescription", ParameterDirection.Input, false, DBNull.Value));
                else
                    _Command.Parameters.Add(DBHelper.CreateTextParameter("@tDescription", ParameterDirection.Input, false, description));
                #endregion description
                _Command.Parameters.Add(DBHelper.CreateFloatParameter("@dLongitude", ParameterDirection.Input, false, longitude));
                _Command.Parameters.Add(DBHelper.CreateFloatParameter("@dLatitude", ParameterDirection.Input, false, latitude));
                _Command.Parameters.Add(DBHelper.CreateFloatParameter("@dAltitude", ParameterDirection.Input, false, altitude));
                #region Satellites
                if (CountSatellites != Int32.MinValue)
                    _Command.Parameters.Add(DBHelper.CreateIntParameter("@nSatellites", ParameterDirection.Input, false, CountSatellites));
                else
                    _Command.Parameters.Add(DBHelper.CreateIntParameter("@nSatellites", ParameterDirection.Input, false, DBNull.Value));
                #endregion 
                #region Time
                if (dTime != DateTime.MinValue)
                    _Command.Parameters.Add(DBHelper.CreateDateTimeParameter("@dTime", ParameterDirection.Input, false, dTime));
                else
                    _Command.Parameters.Add(DBHelper.CreateDateTimeParameter("@dTime", ParameterDirection.Input, false, DBNull.Value));
                #endregion 
                #region Angle
                if (angle != float.MinValue)
                    _Command.Parameters.Add(DBHelper.CreateFloatParameter("@fAngle", ParameterDirection.Input, false, angle));
                else
                    _Command.Parameters.Add(DBHelper.CreateFloatParameter("@fAngle", ParameterDirection.Input, false, DBNull.Value));
                #endregion
                _Command.Parameters.Add(DBHelper.CreateTextParameter("@Color", ParameterDirection.Input, false, color));
                _Command.Parameters.Add(DBHelper.CreateIntParameter("@PositionId", ParameterDirection.ReturnValue, false, 0));
                _Command.ExecuteNonQuery();
                _Transaction.Commit();
                return (Int32)_Command.Parameters["@PositionId"].Value;

            }
            catch (Exception ex)
            {
                if (_Transaction != null)
                    _Transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (_Command != null) _Command.Connection.Close();
            }
        }
        public static void Update(int PositionId, double longitude, double latitude, double altitude, string description, DateTime dTime, int CountSatellites, double angle, string color)
        {
            SqlCommand _Command = null;
            SqlTransaction _Transaction = null;
            try
            {
                _Command = new SqlCommand("sp_Positions_Update", DBHelper.GetConnection());
                _Transaction = _Command.Connection.BeginTransaction();
                _Command.Transaction = _Transaction;
                _Command.CommandType = CommandType.StoredProcedure;
                _Command.Parameters.Add(DBHelper.CreateIntParameter("@PositionId", ParameterDirection.Input, false, PositionId));
                #region description
                if (string.IsNullOrEmpty(description))
                    _Command.Parameters.Add(DBHelper.CreateTextParameter("@tDescription", ParameterDirection.Input, false, DBNull.Value));
                else
                    _Command.Parameters.Add(DBHelper.CreateTextParameter("@tDescription", ParameterDirection.Input, false, description));
                #endregion description
                _Command.Parameters.Add(DBHelper.CreateFloatParameter("@dLongitude", ParameterDirection.Input, false, longitude));
                _Command.Parameters.Add(DBHelper.CreateFloatParameter("@dLatitude", ParameterDirection.Input, false, latitude));
                _Command.Parameters.Add(DBHelper.CreateFloatParameter("@dAltitude", ParameterDirection.Input, false, altitude));
                #region Satellites
                if (CountSatellites != Int32.MinValue)
                    _Command.Parameters.Add(DBHelper.CreateIntParameter("@nSatellites", ParameterDirection.Input, false, CountSatellites));
                else
                    _Command.Parameters.Add(DBHelper.CreateIntParameter("@nSatellites", ParameterDirection.Input, false, DBNull.Value));
                #endregion
                #region Time
                if (dTime != DateTime.MinValue)
                    _Command.Parameters.Add(DBHelper.CreateDateTimeParameter("@dTime", ParameterDirection.Input, false, dTime));
                else
                    _Command.Parameters.Add(DBHelper.CreateDateTimeParameter("@dTime", ParameterDirection.Input, false, DBNull.Value));
                #endregion
                #region Angle
                if (angle != float.MinValue)
                    _Command.Parameters.Add(DBHelper.CreateFloatParameter("@fAngle", ParameterDirection.Input, false, angle));
                else
                    _Command.Parameters.Add(DBHelper.CreateFloatParameter("@fAngle", ParameterDirection.Input, false, DBNull.Value));
                #endregion
                _Command.Parameters.Add(DBHelper.CreateTextParameter("@Color", ParameterDirection.Input, false, color));
                
                _Command.ExecuteNonQuery();
                _Transaction.Commit();
        
            }
            catch (Exception ex)
            {
                if (_Transaction != null)
                    _Transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (_Command != null) _Command.Connection.Close();
            }
        }
        /// <summary>THIS METHOD IS USED TO SET THE PARENT OF A SPECIFIC POLE. </summary>
        public static void SetParent(int PositionId, int ParentPositionId) {
            SqlCommand _Command = null;
            SqlTransaction _Transaction = null;
            try
            {
                _Command = new SqlCommand("sp_Positions_SetParent", DBHelper.GetConnection());
                _Transaction = _Command.Connection.BeginTransaction();
                _Command.Transaction = _Transaction;
                _Command.CommandType = CommandType.StoredProcedure;
                _Command.Parameters.Add(DBHelper.CreateIntParameter("@PositionId", ParameterDirection.Input, false, PositionId));
                _Command.Parameters.Add(DBHelper.CreateIntParameter("@ParentPositionId", ParameterDirection.Input, false, ParentPositionId));
                _Command.ExecuteNonQuery();
                _Transaction.Commit();
            }
            catch (Exception ex)
            {
                if (_Transaction != null)
                    _Transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (_Command != null) _Command.Connection.Close();
            }
        }
        public static void Delete(int PositionId)
        {
            SqlCommand _Command = null;
            SqlTransaction _Transaction = null;
            try
            {
                _Command = new SqlCommand("sp_Positions_Delete", DBHelper.GetConnection());
                _Transaction = _Command.Connection.BeginTransaction();
                _Command.Transaction = _Transaction;
                _Command.CommandType = CommandType.StoredProcedure;
                _Command.Parameters.Add(DBHelper.CreateIntParameter("@PositionId", ParameterDirection.Input, false, PositionId));
                _Command.ExecuteNonQuery();
                _Transaction.Commit();
            }
            catch (Exception ex)
            {
                if (_Transaction != null)
                    _Transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (_Command != null) _Command.Connection.Close();
            }
        }
        
        public static int InsertPositionImages(Int32 IdPosition, string Name, byte[] ImageBytes)
        {
            SqlCommand _Command = null;
            _Command = new SqlCommand("sp_Positions_InsertImages", DBHelper.GetConnection());
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.Parameters.Add(DBHelper.CreateIntParameter("@IdPosition", ParameterDirection.Input, false, IdPosition));
            _Command.Parameters.Add(DBHelper.CreateNVarCharParameter("@Name", 255, ParameterDirection.Input, false, Name));
            _Command.Parameters.Add(DBHelper.CreateImageParameter("@ImageBytes", ImageBytes.Length, ParameterDirection.Input, false, ImageBytes));
            _Command.Parameters.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.ReturnValue, false, 0));
            _Command.ExecuteNonQuery();
            return (Int32)_Command.Parameters["@ImageId"].Value;
        }

        public static void DeletePositionImage(Int32 IdImage)
        {
            SqlCommand _Command = null;
            _Command = new SqlCommand("sp_Positions_DeleteImage", DBHelper.GetConnection());
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.Parameters.Add(DBHelper.CreateIntParameter("@IdImage", ParameterDirection.Input, false, IdImage));
            _Command.ExecuteNonQuery();
        }
        public static void DeleteLocalData()
        { 
            SqlCommand _Command = null;
            SqlTransaction _Transaction = null;
            try
            {
                _Command = new SqlCommand("sp_Positions_DeleteAll", DBHelper.GetConnection());
                _Transaction = _Command.Connection.BeginTransaction();
                _Command.Transaction = _Transaction;

                _Command.CommandType = CommandType.StoredProcedure;
                              
                _Command.ExecuteNonQuery();
                _Transaction.Commit();
            }
            catch (Exception ex)
            {
                if (_Transaction != null)
                    _Transaction.Rollback();
                throw ex;
            }
            finally
            {
                if (_Command != null) _Command.Connection.Close();
            }
        }

        public static DataTable ServerSelectAllPoints()
        {
            SqlDataAdapter _DataAdapter = null;
            try
            {
                _DataAdapter = new SqlDataAdapter("sp_Points_SelectAll", DBHelper.GetMasterConnection());
                _DataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable _Dt = new DataTable();
                _DataAdapter.Fill(_Dt);
                return _Dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_DataAdapter != null) _DataAdapter.SelectCommand.Connection.Close();
            }
        }

    }
}
